"""
成交量策略是量化交易中基于量价关系构建的经典策略，核心通过对比当前成交量与历史平均成交量的倍数关系，并结合股价涨跌幅度生成交易信号。
该策略以 lookback_period（默认 10 天）为回溯周期，计算股票历史成交量的平均值作为参照基准，同时设定 volume_multiple（默认 2.0 倍）作为成交量阈值。
当当日成交量达到或超过平均成交量的 volume_multiple 倍时，若股价上涨，则判定为市场多头力量强劲，策略发出 “买入” 信号；
若股价下跌，则表明空头主导，提示 “卖出” 信号；若股价持平，则建议 “观望”。若当日成交量未达阈值，股价上涨时策略建议 “持有”，股价下跌或持平则提示 “观望”。
该策略通过 Python 代码实现，借助 SQLAlchemy 从数据库获取股票代码、名称、收盘价及成交量数据，利用 matplotlib 可视化呈现信号表格，并对表格样式进行优化，使信号展示更直观。
其逻辑简单且贴近市场供需本质，能有效捕捉成交量异动带来的交易机会，适用于市场情绪波动明显的行情，为投资者提供量化的交易决策依据 。
"""
import pandas as pd
from sqlalchemy import create_engine
import matplotlib.pyplot as plt
from datetime import datetime
import numpy as np

# 创建数据库连接引擎
engine = create_engine('mysql+pymysql://root:@127.0.0.1:3306/stocks?charset=utf8mb4&use_unicode=1')

# 设置中文字体
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False


def generate_volume_signals(lookback_period=10, volume_multiple=2.0):
    """
    成交量策略信号生成函数
    参数:
        lookback_period: 回溯期(天数)用于计算平均成交量
        volume_multiple: 成交量倍数阈值(相对于平均成交量)
    """
    # 获取股票数据
    query = "SELECT ts_code, name FROM stocks_info"
    stocks = pd.read_sql(query, con=engine)

    data = []
    for _, stock in stocks.iterrows():
        ts_code = stock['ts_code']
        name = stock['name']

        try:
            query = f"""
                SELECT trade_date, close, vol 
                FROM stocks_daily 
                WHERE ts_code = '{ts_code}' 
                ORDER BY trade_date DESC 
                LIMIT {lookback_period + 5}
            """
            df = pd.read_sql(query, con=engine)

            if len(df) < lookback_period + 1:
                signal = f"数据不足({lookback_period + 1}天)"
            else:
                df = df.sort_values('trade_date')

                # 计算指标
                current_close = df['close'].iloc[-1]
                prev_close = df['close'].iloc[-2]
                current_vol = df['vol'].iloc[-1]
                avg_vol = df['vol'].iloc[:-1].mean()  # 排除当天的平均成交量

                price_change_pct = (current_close - prev_close) / prev_close * 100
                vol_ratio = current_vol / avg_vol

                # 生成信号
                if current_vol >= volume_multiple * avg_vol:
                    if price_change_pct > 0:
                        signal = f"买入(价↑{price_change_pct:.2f}%,量{vol_ratio:.1f}x)"
                    elif price_change_pct < 0:
                        signal = f"卖出(价↓{abs(price_change_pct):.2f}%,量{vol_ratio:.1f}x)"
                    else:
                        signal = f"观望(价平,量{vol_ratio:.1f}x)"
                else:
                    if price_change_pct > 0:
                        signal = f"持有(价↑{price_change_pct:.2f}%,量平)"
                    elif price_change_pct < 0:
                        signal = f"观望(价↓{abs(price_change_pct):.2f}%,量平)"
                    else:
                        signal = f"观望(价量均平)"

            data.append([name, ts_code, signal])

        except Exception as e:
            data.append([name, ts_code, f"错误:{str(e)}"])

    df_result = pd.DataFrame(data, columns=['股票名称', '股票代码', '信号'])

    # 动态计算图形尺寸
    num_rows = len(df_result)
    row_height = 0.5
    header_height = 0.8
    footer_space = 0.5
    title_space = 1.0

    fig_height = min(
        title_space + header_height + num_rows * row_height + footer_space,
        30
    )

    # 创建图形
    fig = plt.figure(figsize=(12, fig_height))
    ax = fig.add_subplot(111)
    ax.axis('off')

    # 添加标题和时间戳
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    title = fig.suptitle(
        f'股票成交量策略信号 ({lookback_period}日平均, 成交量阈值:{volume_multiple:.1f}倍)\n生成时间: {current_time}',
        y=0.98,
        fontsize=12,
        fontweight='bold'
    )

    # 计算表格位置
    table_height = 0.9 - (0.004 * num_rows)

    # 创建表格
    table = ax.table(
        cellText=df_result.values,
        colLabels=df_result.columns,
        loc='center',
        bbox=[0.1, 0.05, 0.8, table_height],
        cellLoc='center'
    )

    # 设置表格样式
    table.auto_set_font_size(False)
    table.set_fontsize(10)

    # 设置行高
    for i in range(len(df_result) + 1):
        for j in range(3):
            cell = table[(i, j)]
            cell.set_height(0.06)
            cell.set_edgecolor('lightgray')

    # 设置表头样式
    for j in range(3):
        table[(0, j)].set_facecolor('#40466e')
        table[(0, j)].get_text().set_color('white')
        table[(0, j)].set_fontsize(10)

    # 设置信号单元格颜色
    color_map = {
        '买入': '#ff6b6b',
        '卖出': '#51cf66',
        '观望': '#339af0',
    }

    for i in range(1, len(df_result) + 1):
        signal = df_result.iloc[i - 1]['信号']
        color = 'white'
        for key, val in color_map.items():
            if key in signal:
                color = val
                break

        for j in range(3):
            table[(i, j)].set_facecolor(color)
            if color != 'white':
                table[(i, j)].get_text().set_color('white')

    # 调整布局
    plt.tight_layout(rect=[0, 0, 1, 0.96])
    plt.subplots_adjust(top=0.92, bottom=0.05)

    plt.show()


# 生成成交量策略信号表格
generate_volume_signals(lookback_period=10, volume_multiple=2.0)